Table of Contents¶
Please click on the link to jump to specific section
- Overview
- Business Objectives
- Understanding Data
- Identification of variables and data types
- Fixing the rows and columns
- Missing value treatment
- Outlier treatment
- Standardising values
- Categorical Unordered Univariate Analysis
- Categorical Ordered Univariate Analysis
- Numerical Bivariate and Multivariate Analysis
- Categorical Bivariate and Multivariate Analysis
- Correlation Analysis
EDA Credit Case study Overivew ¶
Risk analytics in banking and financial services and understand how data is used to minimise the risk of losing money while lending to customers
Two types of risks are associated with the bank's decision:
- If the applicant is likely to repay the loan, then not approving the loan result in a loss of business to the company
- If the applicant is not likely to repay the loan, i.e., he/she is likely to default, then approving the loan may lead to a financial loss for the company
Below information can be taken from loan application
- The client with payment difficulties
- All other cases
Four types of decisions that could be taken by client/company
- Approved
- Cancelled
- Refused
- Unused offer
Business Objectives ¶
1. Identify the variable which are strong indicator of default
2. These variable will be utilized in portfolio and risk assessmentUnderstanding Data ¶
- 'application_data.csv' contains all the information of the client at the time of application. The data is about whether a client has payment difficulties.
- 'previous_application.csv' contains information about the client’s previous loan data. It contains the data whether the previous application had been Approved, Cancelled, Refused or Unused offer.
- 'columns_description.csv' is data dictionary which describes the meaning of the variables
Identification of variables and data types¶
In [1845]:
# Filtering out the warnings
import warnings
warnings.filterwarnings('ignore')
In [1846]:
# All library imports
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import plotly.express as px
In [1847]:
# Reading csv and assign to variable
# For simplicity, let's call dataframes as 'application_df' and 'prev_application_df'
application_df = pd.read_csv('application_data.csv')
prev_application_df = pd.read_csv('previous_application.csv')
In [1848]:
application_df.head()
Out[1848]:
In [1849]:
prev_application_df.head()
Out[1849]:
In [1850]:
application_df.shape
Out[1850]:
In [1851]:
prev_application_df.shape
Out[1851]:
In [1852]:
application_df.info()
In [1853]:
prev_application_df.info()
In [1854]:
# Numerical values
application_df.describe(include=[np.number])
Out[1854]:
In [1855]:
# Numerical and categorical values
application_df.describe(include='all')
Out[1855]:
In [1856]:
# Numerical values
prev_application_df.describe(include=[np.number])
Out[1856]:
In [1857]:
# Numerical and Categorical values
prev_application_df.describe(include='all')
Out[1857]:
In [1858]:
application_df.dtypes
Out[1858]:
In [1859]:
# since we are unable to see all columns
for column in application_df:
print(column,'\t', application_df[column].dtypes)
In [1860]:
# since we are unable to see all columns
for column in prev_application_df:
print(column,'\t', prev_application_df[column].dtypes)
Fixing the rows and columns¶
From the observation, application_df has 124 columns, of which there are many irrelevant columns that may not be required for analysis and are off the objective of the analysis. In this section we will remove such columns first and then we will remove the rows which are insufficient for analysis
Example: EXT_SOURCE_1, EXT_SOURCE_2, EXT_SOURCE_3 and so on
In [1861]:
# marking irrelevant columns (mostly 0 or no information) and removing it
irrelevant_columns = [i for i in range (41,122,1)]
application_df.drop(application_df.columns[irrelevant_columns], axis=1, inplace=True)
In [1862]:
# verification
print(application_df.shape)
application_df.head()
Out[1862]:
In [1863]:
prev_application_df.head()
Out[1863]:
In [1864]:
application_df['AMT_GOODS_PRICE']= application_df['AMT_GOODS_PRICE'].apply(lambda x:round(x,2))
In [1865]:
application_df['AMT_GOODS_PRICE']
Out[1865]:
Missing value treatment ¶
In [1866]:
# finding null values through out the dataframe
application_df.isnull().sum()
Out[1866]:
In [1867]:
application_df.shape
Out[1867]:
In [1868]:
# from the above 2 cells, we can see that column 'OCCUPATION_TYPE' and 'OWN_CAR_AGE' contains
#significantly amount of null value
# let's inspect 'OWN_CAR_AGE': Age of client's car
application_df['OWN_CAR_AGE'].isna().sum()
Out[1868]:
In [1869]:
# seems like nan is for the clients who never own car, we may create new column 'HAS_OWN_CAR', as it will be
# useful to derive more insights
application_df['HAS_OWN_CAR'] = np.where(application_df['OWN_CAR_AGE'].isnull(), False, True)
In [1870]:
# verification
application_df[application_df['HAS_OWN_CAR']==False]
Out[1870]:
In [1871]:
# let's inspect 'OCCUPATION_TYPE'
application_df['OCCUPATION_TYPE'].isna().sum()
Out[1871]:
In [1872]:
application_df['OCCUPATION_TYPE'].unique()
Out[1872]:
In [1873]:
# from the above observation, null values are significantly high,
# seems like the client refrain to tell information, low chances of human error
# let's call all those null values as 'Others'
application_df['OCCUPATION_TYPE'].fillna('Others', inplace=True)
In [1874]:
# verification
application_df['OCCUPATION_TYPE'].isna().sum()
Out[1874]:
In [1875]:
application_df['OCCUPATION_TYPE'].unique()
Out[1875]:
In [1876]:
# let's inspect column 'NAME_TYPE_SUITE': Who was accompanying client when he was applying for the loan
application_df['NAME_TYPE_SUITE'].unique()
Out[1876]:
In [1877]:
application_df['NAME_TYPE_SUITE'].isna().sum()
Out[1877]:
In [1878]:
# value is insignificantly low; let's see what are the most used values because its a categorical variable
application_df['NAME_TYPE_SUITE'].value_counts()
Out[1878]:
In [1879]:
# most of the applications has 'NAME_TYPE_SUITE' as 'Unaccompanied', so let's fill it with 'Unaccompanied'
application_df['NAME_TYPE_SUITE'].fillna('Unaccompanied', inplace=True)
In [1880]:
# verification
application_df['NAME_TYPE_SUITE'].unique()
Out[1880]:
In [1881]:
application_df['NAME_TYPE_SUITE'].isna().sum()
Out[1881]:
In [1882]:
# let inspect Column 'AMT_GOODS_PRICE': For consumer loans it is the price of the goods for which the loan is given
application_df['AMT_GOODS_PRICE'].isna().sum()
Out[1882]:
In [1883]:
application_df[application_df['AMT_GOODS_PRICE'].isna() & application_df['TARGET']==1]
Out[1883]:
In [1884]:
# only 21 clients are having difficulty in paying loans and all of the loans are revolving loans
# that is why there no value for AMT_GOODS_PRICE
In [1885]:
# let's inspect AMT_ANNUITY
application_df[application_df['AMT_ANNUITY'].isnull()]
Out[1885]:
In [1886]:
#since only insignificant amount of null values, so dropping off those
application_df = application_df[~application_df['AMT_ANNUITY'].isnull()]
In [1887]:
# verification
application_df['AMT_ANNUITY'].isnull().sum()
Out[1887]:
In [1888]:
# inspecting prev_application_df
prev_application_df.shape
Out[1888]:
In [1889]:
prev_application_df.isnull().sum()
Out[1889]:
In [1890]:
# value is insignificantly low; let's see what are the most used values because its a categorical variable
prev_application_df['NAME_TYPE_SUITE'].value_counts()
Out[1890]:
In [1891]:
# most of the applications has 'NAME_TYPE_SUITE' as 'Unaccompanied', so let's fill it with 'Unaccompanied'
prev_application_df['NAME_TYPE_SUITE'].fillna('Unaccompanied', inplace=True)
In [1892]:
# verification
prev_application_df['NAME_TYPE_SUITE'].unique()
Out[1892]:
In [1893]:
prev_application_df['NAME_TYPE_SUITE'].isnull().sum()
Out[1893]:
In [1894]:
# since most of the data missing from columns RATE_INTEREST_PRIMARY and RATE_INTEREST_PRIVILEGED,
# hence droping those columns
prev_application_df.drop(columns=['RATE_INTEREST_PRIMARY','RATE_INTEREST_PRIVILEGED'], inplace=True)
In [1895]:
prev_application_df.head()
Out[1895]:
In [1896]:
# since most of the data missing from columns
#DAYS_FIRST_DRAWING
#DAYS_FIRST_DUE
#DAYS_LAST_DUE_1ST_VERSION
#DAYS_LAST_DUE
#DAYS_TERMINATION
#NFLAG_INSURED_ON_APPROVAL
# hence droping these columns
prev_application_df.drop(columns = ['DAYS_FIRST_DRAWING','DAYS_FIRST_DUE','DAYS_LAST_DUE_1ST_VERSION',
'DAYS_LAST_DUE', 'DAYS_TERMINATION', 'NFLAG_INSURED_ON_APPROVAL'],
inplace = True)
In [1897]:
prev_application_df.head()
Out[1897]:
In [1898]:
# AMT_DOWN_PAYMENT and RATE_DOWN_PAYMENT are missing significantly and cannot be assume some value,
# hence dropping those columns
prev_application_df.drop(columns = ['AMT_DOWN_PAYMENT', 'RATE_DOWN_PAYMENT'],
inplace = True)
In [1899]:
prev_application_df.head()
Out[1899]:
In [1900]:
prev_application_df.isnull().sum()
Out[1900]:
In [1901]:
prev_application_df.shape
Out[1901]:
In [1902]:
# Columns AMT_ANNUITY, AMT_GOODS_PRICE, CNT_PAYMENT are almost 20% null
# removing those rows
prev_application_df = prev_application_df[~prev_application_df['AMT_ANNUITY'].isnull()]
prev_application_df = prev_application_df[~prev_application_df['AMT_GOODS_PRICE'].isnull()]
prev_application_df = prev_application_df[~prev_application_df['CNT_PAYMENT'].isnull()]
In [1903]:
# verification
prev_application_df.isnull().sum()
Out[1903]:
In [1904]:
prev_application_df.shape
Out[1904]:
In [1905]:
application_df.shape
Out[1905]:
In [1906]:
# let's inspect column AMT_INCOME_TOTAL from application_df
application_df.AMT_INCOME_TOTAL.describe().apply("{0:.2f}".format)
Out[1906]:
In [1907]:
plt.figure(figsize=[20,5])
sns.boxplot(application_df.AMT_INCOME_TOTAL)
plt.show()
In [1908]:
application_df.AMT_INCOME_TOTAL.quantile([0.05,0.10,0.5,0.7,0.85, 0.9,0.95, 0.99])
Out[1908]:
In [1909]:
application_df[application_df.AMT_INCOME_TOTAL>337500].describe()
Out[1909]:
In [1910]:
application_df[(application_df.AMT_INCOME_TOTAL>3375000.0) & (application_df.TARGET ==1)]
Out[1910]:
In [1911]:
# from the above, we can remove clients above 95% as they are high earner and only one client is having
# difficulty paying the Installment
application_df = application_df[~(application_df.AMT_INCOME_TOTAL>3375000)]
In [1912]:
#verification
plt.figure(figsize=[20,5])
sns.boxplot(application_df.AMT_INCOME_TOTAL)
plt.show()
In [1913]:
# let's inspect column AMT_CREDIT from application_df
application_df.AMT_CREDIT.describe().apply("{0:.2f}".format)
Out[1913]:
In [1914]:
plt.figure(figsize=[20,5])
sns.boxplot(application_df.AMT_CREDIT)
plt.show()
In [1915]:
application_df.AMT_CREDIT.quantile([0.05,0.10,0.5,0.7,0.85, 0.9,0.95, 0.99])
Out[1915]:
In [1916]:
application_df[(application_df.AMT_CREDIT>1854000.0) & (application_df.TARGET ==1)]
Out[1916]:
In [1917]:
application_df[(application_df.AMT_CREDIT>1854000) & (application_df.TARGET ==0)]
Out[1917]:
In [1918]:
# Although only 124 clients with extremly high credit,are having difficulty paying and 2950 have no issues .
# these are the credit amount, shouldn't be imputed
In [1919]:
# let's inspect column AMT_ANNUITY from application_df
application_df.AMT_ANNUITY.describe().apply("{0:.2f}".format)
Out[1919]:
In [1920]:
plt.figure(figsize=[20,5])
sns.boxplot(application_df.AMT_ANNUITY)
plt.show()
In [1921]:
# let's inspect column AMT_GOODS_PRICE from application_df
application_df.AMT_GOODS_PRICE.describe().apply("{0:.2f}".format)
Out[1921]:
In [1922]:
plt.figure(figsize=[20,5])
sns.boxplot(application_df.AMT_ANNUITY)
plt.show()
In [1923]:
# AMT_GOODS_PRICE and AMT_ANNUITY are the factor that may affect the credit with difficulties;
# further analysis will be done in multivariate analysis section
# hence keeping all the records even outliers at this point of time
Standardising values ¶
In [1924]:
# Rounding off all the numerical values to 2 decimal and transforming FLAGS to Boolean type
In [1925]:
application_df['AMT_INCOME_TOTAL'] = application_df['AMT_INCOME_TOTAL'].apply(lambda x:round(x,2))
In [1926]:
application_df['AMT_CREDIT'] = application_df['AMT_CREDIT'].apply(lambda x:round(x,2))
In [1927]:
application_df['AMT_ANNUITY'] = application_df['AMT_ANNUITY'].apply(lambda x:round(x,2))
In [1928]:
application_df['AMT_GOODS_PRICE'] = application_df['AMT_GOODS_PRICE'].apply(lambda x:round(x,2))
In [1929]:
prev_application_df['AMT_ANNUITY'] = prev_application_df['AMT_ANNUITY'].apply(lambda x:round(x,2))
In [1930]:
prev_application_df['AMT_CREDIT'] = prev_application_df['AMT_CREDIT'].apply(lambda x:round(x,2))
In [1931]:
prev_application_df['AMT_GOODS_PRICE'] = prev_application_df['AMT_GOODS_PRICE'].apply(lambda x:round(x,2))
In [1932]:
application_df['FLAG_OWN_CAR'].unique()
Out[1932]:
In [1933]:
application_df['FLAG_OWN_CAR'] = application_df['FLAG_OWN_CAR'].apply(lambda x : True if x=='Y' else False)
In [1934]:
application_df['FLAG_OWN_CAR'].unique()
Out[1934]:
In [1935]:
application_df['FLAG_OWN_CAR'].dtype
Out[1935]:
In [1936]:
application_df['FLAG_OWN_REALTY'].unique()
Out[1936]:
In [1937]:
application_df['FLAG_OWN_REALTY']=application_df['FLAG_OWN_REALTY'].apply(lambda x : True if x=='Y' else False)
In [1938]:
application_df['FLAG_OWN_REALTY'].unique()
Out[1938]:
In [1939]:
application_df['FLAG_OWN_CAR'].dtype
Out[1939]:
In [1940]:
application_df['FLAG_OWN_REALTY'].unique()
Out[1940]:
In [1941]:
application_df['AGE'] = application_df['DAYS_BIRTH'].apply(lambda x: round(abs(x/365)))
In [1942]:
application_df['AGE']
Out[1942]:
Categorical Unordered Univariate Analysis¶
Unordered variable in application_df¶
- TARGET
- CODE_GENDER
- FLAG_OWN_CAR
- FLAG_OWN_REALTY
- NAME_FAMILY_STATUS
In [1943]:
application_df['TARGET'].value_counts()
Out[1943]:
In [1944]:
application_df['TARGET'].value_counts(normalize=True)
Out[1944]:
In [1945]:
application_df['TARGET'].value_counts(normalize=True).plot.barh()
plt.show()
In [1946]:
application_df['CODE_GENDER'].value_counts()
Out[1946]:
In [1947]:
application_df['CODE_GENDER'].value_counts(normalize=True)
Out[1947]:
In [1948]:
application_df['CODE_GENDER'].value_counts(normalize=True).plot.barh()
plt.show()
In [1949]:
application_df['FLAG_OWN_CAR'].value_counts()
Out[1949]:
In [1950]:
application_df['FLAG_OWN_CAR'].value_counts(normalize=True)
Out[1950]:
In [1951]:
application_df['FLAG_OWN_CAR'].value_counts(normalize=True).plot.barh()
plt.show()
In [1952]:
application_df['FLAG_OWN_REALTY'].value_counts()
Out[1952]:
In [1953]:
application_df['FLAG_OWN_REALTY'].value_counts(normalize=True)
Out[1953]:
In [1954]:
application_df['FLAG_OWN_REALTY'].value_counts(normalize=True).plot.barh()
plt.show()
In [1955]:
application_df['NAME_FAMILY_STATUS'].value_counts()
Out[1955]:
In [1956]:
application_df['NAME_FAMILY_STATUS'].value_counts(normalize=True)
Out[1956]:
In [1957]:
application_df['NAME_FAMILY_STATUS'].value_counts(normalize=True).plot.barh()
plt.show()
Categorical Ordered Univariate Analysis¶
Ordered variable in application_df¶
- NAME_EDUCATION_TYPE
In [1958]:
application_df['NAME_EDUCATION_TYPE'].value_counts()
Out[1958]:
In [1959]:
plt.figure(figsize=[10,10])
application_df['NAME_EDUCATION_TYPE'].value_counts(normalize=True).plot.pie(title="Distribution by Education type")
plt.show()
Numerical Bivariate and Multivariate Analysis¶
In [1960]:
# AMT_INCOME_TOTAL float64
# AMT_CREDIT float64
# AMT_ANNUITY float64
# AMT_GOODS_PRICE
application_df.dtypes
Out[1960]:
In [1961]:
plt.figure(figsize=[10, 5])
plt.scatter(application_df['AMT_ANNUITY'], application_df['AMT_INCOME_TOTAL'])
plt.show()
In [1962]:
plt.figure(figsize=[10, 5])
plt.scatter(application_df['AMT_GOODS_PRICE'], application_df['AMT_CREDIT'])
plt.show()
In [1963]:
sns.pairplot(data=application_df, vars=['AMT_ANNUITY','AMT_GOODS_PRICE','AMT_CREDIT'])
plt.show()
Correlation Analysis ¶
In [1964]:
application_df[['AMT_ANNUITY','AMT_GOODS_PRICE','AMT_CREDIT']].corr()
Out[1964]:
Correlation heatmap
In [1965]:
sns.heatmap(application_df[['AMT_ANNUITY','AMT_GOODS_PRICE','AMT_CREDIT']].corr(), annot=True, cmap='Greens')
Out[1965]:
In [1966]:
# let's analyse TARGET vs AMT_INCOME_TOTAL
application_df.groupby('TARGET')['AMT_INCOME_TOTAL'].aggregate(["mean", "median"]).plot.bar()
Out[1966]:
In [1967]:
# parallel graph
fig = px.parallel_categories(application_df, dimensions=['NAME_CONTRACT_TYPE','NAME_EDUCATION_TYPE','NAME_FAMILY_STATUS', 'TARGET'],
color="AGE", color_continuous_scale=px.colors.sequential.Inferno,
labels={'NAME_CONTRACT_TYPE':'Contract type', 'NAME_EDUCATION_TYPE':'Eductation Level','NAME_FAMILY_STATUS':'Family status', 'TARGET':'Having difficulties'})
fig.show()